#region imported Namespaces
using System;
using System.Data;
using System.Data.SqlClient;

using BO = PHSRAG.Insight21.BusinessObjects;
using Utility = PHSRAG.Utility;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;

#endregion

namespace PHSRAG.Insight21.Agreements
{
    /// <summary>
    /// Summary description for Sponsor.
    /// </summary>
    public class Sponsor : BO.SmartBusinessObject
    {
        #region Constants
        private static string SponsorKey = GenerateKey.MakeKey("Sponsor_0DFF336C-6A1C-4d18-A57A-6B923B592B4A", GenerateKey.Agreement);
        private const string InfoEdSponsorTableName = "InfoEdSponsors";
        #endregion

        #region Constructor
        public Sponsor(ICachePolicy cachePolicy)
            : base(cachePolicy)
        {
        }
        public Sponsor(ICachePolicy cachePolicy, DataRow row)
            : base(cachePolicy)
        {
            PopulateDataMembers(row);
        }
        #endregion

        #region private members
        private const string SponsorDataTableName = "Sponsor";
        private string name;
        private string shortName;
        private string sponsorTypeId;
        private string description;
        private string psCustCode;
        private int rexSponsorId;
        private DateTime dateCreated;
        private string infoEdSponsorType;
        private string sponsorTypeName;
        private DataSet dataContainer;
        private string infoEdSponsorCode;
        private string chargeRate;

        private bool isInfoEdDataLoaded = false;
        private string street;
        private string street2;
        private string city;
        private string state;
        private string zipCode;
        private string country;
        private string emailAddress;
        private decimal icRate;
        #endregion

        #region Properties

        /// <summary>
        /// Get the key to be used in all caching operations for this instance of the object.
        /// </summary>
        public override string CacheKey() { return SponsorKey; }

        /// <summary>
        /// DataContainer
        /// </summary>
        public DataSet DataContainer
        {
            get { return dataContainer; }
            set { dataContainer = value; }
        }

        /// <summary>
        /// The Name of the Sponsor
        /// </summary>
        public string Name
        {
            get
            {
                return name;
            }
            set
            {
                SetDirty(ref name, value);
            }

        }
        /// <summary>
        /// Read-only Name and Type of the Sponsor (Format: Name [Type])
        /// </summary>
        public string NameAndType
        {
            get
            {
                return string.Format("{0} [{1}]", Name, SponsorTypeName);
            }
        }
        /// <summary>
        /// Readonly Short name of the sponsor (Format: Name [Type])
        /// </summary>
        public string ShortNameAndType
        {
            get
            {
                return string.Format("{0} [{1}]", ShortName, SponsorTypeName);
            }
        }

        /// <summary>
        /// The Short name of the sponsor
        /// </summary>
        public string ShortName
        {
            get
            {
                return shortName;
            }
            set
            {
                SetDirty(ref shortName, value);
            }
        }

        /// <summary>
        ///  The description of the Sponsor
        /// </summary>
        public string Description
        {
            get
            {
                return description;
            }
            set
            {
                SetDirty(ref description, value);
            }
        }

        public string ChargeRate
        {
            get
            {
                return chargeRate;
            }
            set
            {
                SetDirty(ref chargeRate, value);
            }
        }

        public string SponsorTypeId
        {
            get
            {
                return sponsorTypeId;
            }
            set
            {
                SetDirty(ref sponsorTypeId, value);
            }
        }

        /// <summary>
        /// The actual name of the sponsor type 
        /// </summary>
        public string SponsorTypeName
        {
            get
            {
                return sponsorTypeName;
            }
            set
            {
                SetDirty(ref sponsorTypeName, value);
            }
        }

        /// <summary>
        /// The InfoEd sponsor type of this sponsor 
        /// </summary>
        public string InfoEdSponsorType
        {
            get
            {
                return infoEdSponsorType;
            }
            set
            {
                SetDirty(ref infoEdSponsorType, value);
            }
        }

        /// <summary>
        /// The Id of the corresponding Sponsor in the Rex database, if applicable.
        /// </summary>
        public int RexSponsorId
        {
            get
            {
                return rexSponsorId;
            }
            set
            {
                SetDirty(ref rexSponsorId, value);
            }
        }

        /// <summary>
        ///  Date the Sponsor was created.
        /// </summary>
        public DateTime DateCreated
        {
            get
            {
                return dateCreated;
            }
        }

        /// <summary>
        ///  PScustCode for sponsor 
        /// </summary>
        public string PSCustCode
        {

            get
            {
                return psCustCode;
            }
            set
            {
                SetDirty(ref psCustCode, value);
            }
        }
        /// <summary>
        /// Street for sponsor from InfoEd 
        /// </summary>
        public string Street
        {
            get
            {
                LoadInfoEdData();
                return street;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref street, value);
            }
        }
        /// <summary>
        /// Street 2 for sponsor from InfoEd 
        /// </summary>
        public string Street2
        {
            get
            {
                LoadInfoEdData();
                return street2;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref street2, value);
            }
        }
        /// <summary>
        /// City for sponsor from InfoEd 
        /// </summary>
        public string City
        {
            get
            {
                LoadInfoEdData();
                return city;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref city, value);
            }
        }
        /// <summary>
        /// Country for sponsor from InfoEd 
        /// </summary>
        public string Country
        {
            get
            {
                LoadInfoEdData();
                return country;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref country, value);
            }
        }
        /// <summary>
        /// State for sponsor from InfoEd 
        /// </summary>
        public string State
        {
            get
            {
                LoadInfoEdData();
                return state;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref state, value);
            }
        }
        /// <summary>
        /// ZipCode for sponsor from InfoEd 
        /// </summary>
        public string ZipCode
        {
            get
            {
                LoadInfoEdData();
                return zipCode;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref zipCode, value);
            }
        }

        /// <summary>
        /// Email Address for sponsor from InfoEd 
        /// </summary>
        public string EmailAddress
        {
            get
            {
                LoadInfoEdData();
                return emailAddress;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref emailAddress, value);
            }
        }

        /// <summary>
        /// ICRate for sponsor from InfoEd 
        /// </summary>
        public decimal ICRate
        {
            get
            {
                LoadInfoEdData();
                return icRate;
            }
            set
            {
                LoadInfoEdData();
                SetDirty(ref icRate, value);
            }
        }
        /// <summary>
        /// SponCode from AdmSponsor 
        /// </summary>
        public string InfoEdSponCode
        {
            get
            {
                return infoEdSponsorCode;
            }
            set
            {
                SetDirty(ref infoEdSponsorCode, value);
            }
        }
        #endregion

        #region Methods
        public override void Cache()
        {
            CachePolicy.Cache(SponsorKey, this);
        }

        public override void UnCache()
        {
            CachePolicy.UnCache(SponsorKey);
        }

        /// <summary>
        /// Saves the Sponsor into the InfoEd, ReX, and Insight databases
        /// </summary>
        /// <param name="insightTransaction">Insight Transaction</param>
        protected override void SaveObject(System.Data.SqlClient.SqlTransaction insightTransaction)
        {
            if (!isInfoEdDataLoaded)
            {
                throw new Exception("InfoEd information was never loaded");
            }

            SqlConnection rexConnection = null;
            SqlTransaction rexTransaction = null;
            SqlConnection infoEdConnection = null;
            SqlTransaction infoEdTransaction = null;

            try
            {
                rexConnection = new SqlConnection(Utility.DataAccess.GetConnectionString("Rex"));
                rexConnection.Open();

                infoEdConnection = new SqlConnection(Utility.DataAccess.GetConnectionString("InfoEdStaging"));
                infoEdConnection.Open();

                rexTransaction = rexConnection.BeginTransaction();
                infoEdTransaction = infoEdConnection.BeginTransaction();


                // create temporary strings to handle quotes in dynamic sql
                string tempName = name.Replace("'", "''");
                string tempDescription = description.Replace("'", "''");
                string tempStreet = street.Replace("'", "''");
                string tempStreet2 = street2.Replace("'", "''");


                // ReX Maintenance
                int rexSponsorIdtemp;
                rexSponsorIdtemp = Convert.ToInt32(Utility.DataAccess.ExecuteScalar(rexConnection, rexTransaction,
                    "spSponsorUpdate", Utility.DataAccess.BuildSqlParameterArray("@nSponsorId", SqlDbType.Int, rexSponsorId,
                    "@sName", SqlDbType.VarChar, name,
                    "@sShortName", SqlDbType.VarChar, shortName,
                    "@sType", SqlDbType.VarChar, sponsorTypeName,
                    "@sNotes", SqlDbType.VarChar, description)));

                if (rexSponsorIdtemp == 0 || rexSponsorIdtemp == -1)
                {
                    string getRexSponsorId = "Select nSponsorId from tblSponsor where sName = '" + tempName + "'";
                    SqlCommand getRexSponsorIdCommand = new SqlCommand(getRexSponsorId);
                    getRexSponsorIdCommand.Connection = rexConnection;
                    getRexSponsorIdCommand.Transaction = rexTransaction;
                    RexSponsorId = Convert.ToInt32(getRexSponsorIdCommand.ExecuteScalar());
                }

                //InfoEd maintenance
                //Return the next largest Id (largest spon_code + 1) from the sponsor table
                //for a new sponsor insert (when the spon_code is empty) 
                if (string.IsNullOrEmpty(infoEdSponsorCode))
                {
                    string id = "SELECT MAX(spon_code) FROM " + InfoEdSponsorTableName;
                    SqlCommand returnLargestId = new SqlCommand(id);
                    returnLargestId.Connection = infoEdConnection;
                    returnLargestId.Transaction = infoEdTransaction;
                    infoEdSponsorCode = (Convert.ToInt32(returnLargestId.ExecuteScalar()) + 1).ToString();
                }

                //Insert or update sponsor record into the InfoEdSponsors view depending on if this sponsor already exists
                string insertUpdate =
                    "IF EXISTS (SELECT * FROM " + InfoEdSponsorTableName + " WHERE spon_code = '" + InfoEdSponCode + "') " +
                    " BEGIN UPDATE " + InfoEdSponsorTableName + " SET Name = '" + tempName + "', Comments = '" + tempDescription + "', Street = '" + tempStreet + "',   Street2 = '" + tempStreet2 + "', City = '" + City + "', State = '" + State + "', ZipCode = '" + ZipCode + "', CountryCode = '" + Country + "', EmailAddress = '" + EmailAddress + "', ICRate = " + ICRate + ", SponsorType = '" + infoEdSponsorType + "' WHERE spon_code = '" + InfoEdSponCode + "' SELECT spon_code FROM " + InfoEdSponsorTableName + " WHERE spon_code = '" + InfoEdSponCode +
                    "' END ELSE BEGIN INSERT INTO  " + InfoEdSponsorTableName + " (inst_code, spon_code, Name, SponsorType, Comments, Street, City, State, ZipCode, EmailAddress, ICRate, CountryCode, Street2) VALUES ('PARTNERS', '" + infoEdSponsorCode + "', '" + tempName + "', '" + infoEdSponsorType + "', '" + tempDescription + "', '" + tempStreet + "', '" + City + "', '" + State + "', '" + ZipCode + "', '" + EmailAddress + "', " + ICRate + ", '" + Country + "', '" + tempStreet2 + "') SELECT spon_code = '" + infoEdSponsorCode + "' END";

                SqlCommand insertUpdateCommand = new SqlCommand(insertUpdate);
                insertUpdateCommand.Connection = infoEdConnection;
                insertUpdateCommand.Transaction = infoEdTransaction;
                infoEdSponsorCode = insertUpdateCommand.ExecuteScalar().ToString();

                //Insert or update peoplesoftcustid into the InfoEdSponsors view depending on if this sponsor already exists
                string updatePSCodeString = "IF EXISTS (SELECT * FROM " + InfoEdSponsorTableName + " WHERE prop_no = '" + infoEdSponsorCode + "') UPDATE " + InfoEdSponsorTableName + " SET  PSCustId = '" + psCustCode + "' WHERE prop_no = '" + infoEdSponsorCode +
                    "' ELSE INSERT " + InfoEdSponsorTableName + " (inst_code_1, prop_no, PSCustId, sub_num)  VALUES ('PARTNERS', '" + infoEdSponsorCode + "' , '" + psCustCode + "', '1')";

                SqlCommand updatePSCode = new SqlCommand(updatePSCodeString);
                updatePSCode.Connection = infoEdConnection;
                updatePSCode.Transaction = infoEdTransaction;
                updatePSCode.ExecuteNonQuery();

                // save to Insight
                object uniqueId =
                       Utility.DataAccess.ExecuteScalar(Utility.DataAccess.GetConnectionString(),
                            "CreateAdmSponsor",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@name", SqlDbType.VarChar, name,
                            "@shortName", SqlDbType.VarChar, shortName,
                            "@description", SqlDbType.VarChar, description,
                            "@admSponsorTypeId", SqlDbType.Int, sponsorTypeId,
                            "@RexSponsorId", SqlDbType.Int, rexSponsorId,
                            "@sponsorId", SqlDbType.Int, Id,
                            "@InfoEdSponCode", SqlDbType.VarChar, infoEdSponsorCode,
                            "@PSCustCode", SqlDbType.VarChar, psCustCode));

                Id = Convert.ToInt32(uniqueId);

                rexTransaction.Commit();
                infoEdTransaction.Commit();
            }
            catch (Exception ex)
            {
                if (null != rexTransaction)
                {
                    rexTransaction.Rollback();
                }

                if (null != infoEdTransaction)
                {
                    infoEdTransaction.Rollback();
                }

                throw new Exception("Failed to save sponsor. Error occurred while trying to execute the SaveObject()", ex);
            }
            finally
            {
                if (null != rexConnection &&
                    rexConnection.State != ConnectionState.Closed)
                {
                    rexConnection.Close();
                }

                if (null != infoEdConnection &&
                    infoEdConnection.State != ConnectionState.Closed)
                {
                    infoEdConnection.Close();
                }
            }
        }

        /// <summary>
        /// Delete is not supported
        /// </summary>
        /// <param name="sqlTransaction">DB Transaction to use during deletion.</param>
        protected override void DeleteObject(System.Data.SqlClient.SqlTransaction sqlTransaction)
        {
            throw new Exception("Deletion of a sponsor is not allowed");
        }
        /// <summary>
        /// Loads a sponsor, also loads the infoed dataset so that 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="args"></param>
        public override void Load(string connectionString, params object[] args)
        {
            if (args.Length != 1)
            {
                throw new Exception("Sponsor Load requires Sponsor ID.");
            }

            int sponsorId = (int)args[0];
            DataContainer = Utility.DataAccess.GetDataSet(
                connectionString,
                "GetSponsor",
                Utility.DataAccess.BuildSqlParameterArray("@sponsorId", SqlDbType.Int, sponsorId));

            if (DataContainer.Tables.Count > 0 &&
                DataContainer.Tables[0].Rows.Count > 0)
            {
                PopulateDataMembers(DataContainer.Tables[0].Rows[0]);
            }
            else
            {
                throw new Exception("Unable to load sponsor");
            }
        }

        /// <summary>
        /// Merges the sponsor of the passed Id into this Sponsor
        /// </summary>
        /// <param name="toRemoveSponsorId">Id of Sponsor to be merged and removed</param>
        public void Merge(int mergeSponsorId)
        {
            if (Id > 0)
            {
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(),
                   "MergeAdmSponsors",
                   Utility.DataAccess.BuildSqlParameterArray(
                      "@fromId", SqlDbType.Int, mergeSponsorId,
                      "@toId", SqlDbType.Int, Id));
            }
            else
            {
                throw new Exception("Sponsor has not yet been saved.");
            }
        }

        /// <summary>
        /// Converts a database column value to a string
        /// </summary>
        /// <param name="columnValue">Value from the database</param>
        /// <returns>An equivalent string</returns>
        private string GetColumnValue(object columnValue)
        {
            return (columnValue == DBNull.Value) ? string.Empty : columnValue.ToString();
        }

        /// <summary>
        /// Loads each of these values from the datarow with the id passed in 
        /// </summary>
        /// <param name="sponsorRow">Sponsor Row</param>
        private void PopulateDataMembers(DataRow sponsorRow)
        {
            try
            {
                Id = ((sponsorRow[0]) == DBNull.Value) ? 0 : Convert.ToInt32((sponsorRow[0]));
                name = GetColumnValue(sponsorRow["Name"]);
                shortName = GetColumnValue(sponsorRow["ShortName"]);
                sponsorTypeName = GetColumnValue(sponsorRow["SponsorType"]);
                description = GetColumnValue(sponsorRow["Description"]);
                psCustCode = GetColumnValue(sponsorRow["PSCustCode"]);
                rexSponsorId = ((sponsorRow["RexSponsorId"]) == DBNull.Value) ? 0 : Convert.ToInt32((sponsorRow["RexSponsorId"]));
                dateCreated = (sponsorRow["DateCreated"] == DBNull.Value) ? DateTime.MinValue : (DateTime)sponsorRow["DateCreated"];
                infoEdSponsorCode = GetColumnValue(sponsorRow["InfoEdSponCode"]);
                sponsorTypeId = GetColumnValue(sponsorRow["AdmSponsorTypeId"]);
                chargeRate = GetColumnValue(sponsorRow["ChargeRate"]);
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to populate sponsor memebers.", ex);
            }
        }


        /// <summary>
        /// Loads each of these fields that are stored in the InfoEd database (but not in the Insight sponsor table) 
        /// </summary>
        /// <param name="sponsorRow"></param>
        private void LoadInfoEdData()
        {
            try
            {
                if (isInfoEdDataLoaded)
                {
                    return;
                }

                if (0 == Id)
                {
                    isInfoEdDataLoaded = true;
                    return;
                }

                SqlConnection infoEdConnection = new SqlConnection(Utility.DataAccess.GetConnectionString("InfoEdStaging"));
                infoEdConnection.Open();
                SqlTransaction infoEdTransaction = infoEdConnection.BeginTransaction();
                DataSet InfoEdDataSet = new DataSet("InfoEdSponsors");
                SqlCommand returnInfoEdDataSet = new SqlCommand("Select * from InfoEdSponsors where spon_code = '" + InfoEdSponCode + "'");
                returnInfoEdDataSet.Connection = infoEdConnection;
                returnInfoEdDataSet.Transaction = infoEdTransaction;
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = returnInfoEdDataSet;
                adapter.Fill(InfoEdDataSet);

                if (InfoEdDataSet.Tables.Count > 0 &&
                    InfoEdDataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow sponsorRow = InfoEdDataSet.Tables[0].Rows[0];
                    street = GetColumnValue(sponsorRow["Street"]);
                    city = GetColumnValue(sponsorRow["City"]);
                    state = GetColumnValue(sponsorRow["State"]);
                    emailAddress = GetColumnValue(sponsorRow["emailAddress"]);
                    zipCode = GetColumnValue(sponsorRow["ZipCode"]);
                    icRate = ((sponsorRow["ICRate"]) == DBNull.Value) ? 0 : Convert.ToDecimal((sponsorRow["ICRate"]));
                    country = GetColumnValue(sponsorRow["CountryCode"]);
                    street2 = GetColumnValue(sponsorRow["Street2"]);
                }

                isInfoEdDataLoaded = true;
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to populate sponsor members.", ex);
            }
        }

        #endregion
    }

}


